// SnapshotC.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include <Windows.h>
#include <iostream>
#include <sql.h>
//#include <sqltypes.h>
#include <sqlext.h>

using namespace std;

SQLCHAR* GetConnectionString()
{
	return reinterpret_cast<SQLCHAR*>("MyConnectionString");
}

void ConnectToSQL(SQLWCHAR* sqlStatement)
{
	SQLHENV env;
	SQLHDBC dbc;
	SQLHSTMT stmt;
	//SQLRETURN ret;
	//SQLSMALLINT columns;
	//int row = 0;

	stmt = sqlStatement;

	/* Allocate an environment handle */
	SQLAllocHandle(SQL_HANDLE_ENV, nullptr, &env);
	/* We want ODBC 3 support */
	SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, reinterpret_cast<void *>(SQL_OV_ODBC3), 0);
	/* Allocate a connection handle */
	SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

	/* Connect to the DSN */
	SQLDriverConnect(dbc, nullptr, GetConnectionString(), SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_COMPLETE);


	/* Check for success */
	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt))
	{
		cout << "Failed to connect";
	}
	else
	{
		cout << "Connected";
	}

	//std::cin.get();

}

void show_error(unsigned int handletype, const SQLHANDLE& handle) 
{
	SQLCHAR sqlstate[1024];

	SQLCHAR message[1024];

	if (SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, nullptr, message, 1024, nullptr))
		cout << "Message: " << message << "\nSQLSTATE: " << sqlstate << endl;
}

void ExecuteSQL(SQLHANDLE sqlconnectionhandle, SQLCHAR* sqlQuery, bool returnvalue)
{
	SQLHANDLE sqlenvhandle;
	//SQLHANDLE sqlconnectionhandle;
	SQLHANDLE sqlstatementhandle;
	SQLRETURN retcode;

	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, nullptr, &sqlenvhandle))
		goto FINISHED;

	if (SQL_SUCCESS != SQLSetEnvAttr(sqlenvhandle, SQL_ATTR_ODBC_VERSION, reinterpret_cast<SQLPOINTER>(SQL_OV_ODBC3), 0))
		goto FINISHED;

	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle))
		goto FINISHED;

	SQLCHAR retconstring[1024];

	
	switch (SQLDriverConnect(&sqlconnectionhandle, nullptr, GetConnectionString(), SQL_NTS, retconstring, 1024, nullptr, SQL_DRIVER_NOPROMPT))
	{
	case SQL_SUCCESS_WITH_INFO:
		show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
		break;
	case SQL_INVALID_HANDLE:
	case SQL_ERROR:
		show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
		goto FINISHED;
	default:
		break;
	}

	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle))
		goto FINISHED;

	if (SQL_SUCCESS == SQLExecDirect(&sqlstatementhandle, sqlQuery, SQL_NTS) && returnvalue) 
	{
		//char name[64];
		//char address[64];
		//int id;

		char name[64];
	
		while (SQLFetch(&sqlstatementhandle) == SQL_SUCCESS)
		{
			//SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &id, 0, nullptr);
			//SQLGetData(sqlstatementhandle, 2, SQL_C_CHAR, name, 64, nullptr);
			//SQLGetData(sqlstatementhandle, 3, SQL_C_CHAR, address, 64, nullptr);
			//cout << id << " " << name << " " << address << endl;
			SQLGetData(&sqlstatementhandle, 1, SQL_C_CHAR, name, 64, nullptr);
			cout << name << endl;
		}
	}
	else
	{
		show_error(SQL_HANDLE_STMT, &sqlstatementhandle);
		goto FINISHED;
	}

FINISHED:
	SQLFreeHandle(SQL_HANDLE_STMT, &sqlstatementhandle);
	//SQLDisconnect(sqlconnectionhandle);
	//SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
	SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);

}

void CleanUpSQLConnection(SQLHANDLE sqlconnectionhandle)
{
	SQLDisconnect(sqlconnectionhandle);
	SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
}

void RunDmcWithTrans()
{
	SQLHANDLE sqlconnectionhandle1 = nullptr;
	SQLHANDLE sqlconnectionhandle2 = nullptr;
	SQLHANDLE sqlconnectionhandle3 = nullptr;

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("IF EXISTS (SELECT * FROM sys.tables WHERE name=N'snapTest') "
		           "DROP TABLE snapTest"), false);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("CREATE TABLE snapTest ([id] INT IDENTITY, col1 autoCHAR(15))"), false);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("INSERT INTO snapTest VALUES('Niels')"), false);

	ExecuteSQL(sqlconnectionhandle3, reinterpret_cast<SQLCHAR*>("SET TRANSACTION ISOLATION "
		           "LEVEL SNAPSHOT "
		           "BEGIN TRAN "
		           "SELECT col1 FROM snapTest WHERE id = 1"), true);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("SET TRANSACTION ISOLATION "
		           "LEVEL SNAPSHOT "
		           "BEGIN TRAN "
		           "UPDATE snapTest "
		           "SET col1 = 'NewNiels' "
		           "WHERE id = 1 "), false);

	ExecuteSQL(sqlconnectionhandle2, reinterpret_cast<SQLCHAR*>("SET TRANSACTION ISOLATION LEVEL SNAPSHOT "
		           "BEGIN TRAN "
		           "SELECT col1 FROM snapTest "
		           "WHERE id = 1"), false); //receives value 'Niels'

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("COMMIT TRAN"), false);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("SELECT col1 FROM snapTest WHERE id = 1"), true); //receives value 'Niels'

	ExecuteSQL(sqlconnectionhandle2, reinterpret_cast<SQLCHAR*>("COMMIT TRAN"), false);

	ExecuteSQL(sqlconnectionhandle2, reinterpret_cast<SQLCHAR*>("SELECT col1 FROM snapTest WHERE id = 1"), true); //receives value 'NewNiels'

	ExecuteSQL(sqlconnectionhandle3, reinterpret_cast<SQLCHAR*>("COMMIT TRAN"), false);

	CleanUpSQLConnection(sqlconnectionhandle1);
	CleanUpSQLConnection(sqlconnectionhandle2);
	CleanUpSQLConnection(sqlconnectionhandle3);
}

void RunDmcWithoutTrans()
{
	SQLHANDLE sqlconnectionhandle1 = nullptr;
	SQLHANDLE sqlconnectionhandle2 = nullptr;
	SQLHANDLE sqlconnectionhandle3 = nullptr;

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("IF EXISTS "
		           "(SELECT * FROM sys.tables WHERE name=N'snapTest') "
		           "DROP TABLE snapTest"), false);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("CREATE TABLE snapTest ([id] INT IDENTITY, col1 autoCHAR(15))"), false);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("INSERT INTO snapTest VALUES('Niels')"), false);

	ExecuteSQL(sqlconnectionhandle3, reinterpret_cast<SQLCHAR*>("SET TRANSACTION ISOLATION "
		           "LEVEL SNAPSHOT "
		           "BEGIN TRAN "
		           "SELECT col1 FROM snapTest WHERE id = 1"), true);

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("UPDATE snapTest "
		           "SET col1 = 'NewNiels' "
		           "WHERE id = 1 "), false);

	ExecuteSQL(sqlconnectionhandle2, reinterpret_cast<SQLCHAR*>("SELECT col1 FROM snapTest "
		           "WHERE id = 1"), false); //receives value 'Niels'

	ExecuteSQL(sqlconnectionhandle1, reinterpret_cast<SQLCHAR*>("SELECT col1 FROM snapTest WHERE id = 1"), true); //receives value 'Niels'

	ExecuteSQL(sqlconnectionhandle2, reinterpret_cast<SQLCHAR*>("SELECT col1 FROM snapTest WHERE id = 1"), true); //receives value 'NewNiels'

	ExecuteSQL(sqlconnectionhandle3, reinterpret_cast<SQLCHAR*>("COMMIT TRAN"), false);

	CleanUpSQLConnection(sqlconnectionhandle1);
	CleanUpSQLConnection(sqlconnectionhandle2);
	CleanUpSQLConnection(sqlconnectionhandle3);
}

int main()
{
	RunDmcWithTrans();
	return 0;
}
